from typing import Union

from sqlalchemy import update, CursorResult, text, func
from sqlmodel import select, desc, col, asc
from sqlmodel.sql._expression_select_cls import SelectOfScalar, Select

from src.base.beans import PageResult
from src.config.db import mysqlClient
from src.domain.sys_role import SysRole, SysRoleReq
from src.domain.sys_user_role import SysUserRole


def get_role_by_id(role_id: int) -> SysRole:
    return mysqlClient.exec(select(SysRole).where(SysRole.role_id == role_id).where(SysRole.del_flag == False)).first()


def where_handle(stmt: Union[Select, SelectOfScalar], entity: SysRole = None):
    stmt.where(SysRole.del_flag == False)
    if entity:
        if entity.role_id:
            stmt = stmt.where(SysRole.role_id == entity.role_id)
        if entity.role_name:
            stmt = stmt.where(SysRole.role_name == entity.role_name)
        if entity.role_code:
            stmt = stmt.where(SysRole.role_code == entity.role_code)
        if entity.data_scope:
            stmt = stmt.where(SysRole.data_scope == entity.data_scope.value)
        if entity.status:
            stmt = stmt.where(SysRole.status == entity.status)
    return stmt


def get_role_list_by_condition(entity: SysRole = None) -> list[SysRole]:
    stmt = select(SysRole)
    stmt = where_handle(stmt, entity)
    # order_by() 是返回一个新的语句对象，而不是原地修改
    stmt = stmt.order_by(desc(SysRole.sort))
    users = mysqlClient.exec(stmt)
    return users


def get_role_page_by_condition(entity: SysRoleReq = None) -> PageResult:
    search = SysRole(**entity.model_dump())
    list_stmt = select(SysRole)
    list_stmt = where_handle(list_stmt, search)
    if entity.sorter:
        list_stmt = list_stmt.order_by(text(entity.sorter))
    else:
        list_stmt = list_stmt.order_by(asc(SysRole.sort))
    list_stmt = list_stmt.limit(entity.size).offset(entity.size * (entity.current - 1))
    users = mysqlClient.exec(list_stmt).all()

    total_stmt = select(func.count(SysRole.user_id))
    total_stmt = where_handle(total_stmt, search)
    total_result = mysqlClient.exec(total_stmt)
    total = total_result.one_or_none()
    return PageResult(total=total, data=users, current=entity.current, size=entity.size)



def insert_role(entity: SysRole = None):
    entity.creator = entity.creator if entity.creator else 1

    mysqlClient.add(entity)


def update_role(entity: SysRole = None) -> int:
    stmt = update(SysRole).values(entity.model_dump()).where(col(SysRole.role_id).is_(entity.role_id))
    result: CursorResult = mysqlClient.exec(stmt)
    mysqlClient.commit()
    return result.rowcount()


def delete_role(role_id: int = None) -> int:
    stmt = update(SysRole).values(SysRole(del_flag=True).model_dump()).where(col(SysRole.role_id).is_(role_id))
    result: CursorResult = mysqlClient.exec(stmt)
    mysqlClient.commit()
    return result.rowcount()


def delete_roles(role_ids: list[int] = None) -> int:
    stmt = update(SysRole).values(SysRole(del_flag=True).model_dump()).where(col(SysRole.role_id).in_(role_ids))
    result: CursorResult = mysqlClient.exec(stmt)
    mysqlClient.commit()
    return result.rowcount()


# def get_role_list_by_user_id(user_id: int):
#     role_sql = text(
#         "select r.* from sys_role r left join sys_user_role ur on r.role_id = ur.role_id where ur.user_id = :user_id")
#     role_stmt = select(SysRole).from_statement(role_sql)
#     return mysqlClient.exec(role_stmt, params={"user_id": user_id}).scalars().all()

def get_role_list_by_user_id(user_id: int):
    stmt = (select(SysRole)
            .select_from(SysRole)
            .outerjoin(SysUserRole, col(SysUserRole.role_id).__eq__(SysRole.role_id))
            .where(SysUserRole.user_id == user_id))
    return mysqlClient.exec(stmt).all()
